package com.jietu.mapper;

import com.jietu.entity.Vo;
import com.jietu.entity.Vo1;
import com.jietu.entity.Vo2;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
/**
 * @author LQ&LYP lq
 * @ClassName VoMapper
 * @description: TODO
 * @date 2024年12月25日
 * @version: 1.0
 */
@Mapper
public interface VoMapper {
    /**
     *  1.1流量分时统计（页面浏览量）测试
     */
    @Select(" select     \n" +
            "\t\thour(ucp.create_time)  name ,\n" +
            "    count(*)   value      \n" +
            "\t\tfrom    user_chapter_process   ucp \n" +
            "    group  by    name   order  by  name")
    public  List<Vo>  getList1();
    /**
     * 1.2各课程考试统计测试
     */
    @Select(" select \t \n" +
            "\t\t  course_info.course_name `name`,\n" +
            "\t    count(test_exam.user_id)   value ,\n" +
            "\t\t\tsum(test_exam.score)/count(test_exam.user_id)  avgScore,\n" +
            "\t\t\tsum(test_exam.duration_sec)/count(test_exam.user_id)  avgTime\t\t\t\n" +
            "\t  \tfrom   course_info  \n" +
            "\t\t  left  join   test_paper on course_info.id=test_paper.course_id\n" +
            "\t\t\tleft  join   test_exam  on   test_exam.paper_id=test_paper.id\n" +
            "\t\t\tgroup  by   course_info.course_name")
    public  List<Vo1>  getList2();
    /**
     * 1.3新增交易用户统计（支付成功人数）测试
     */
    @Select("select   count(*)  from   payment_info")
    public  Double  getList3();
    /**
     * 1.4评价情况统计测试
     */
    @Select("select \n" +
            "\t\t\tcourse_name name,\n" +
            "       sum(review_stars)/count(review_info.user_id) avgScore,\n" +
            "\t\t\t count(review_info.user_id)  totalNum,\n" +
            "\t\t\t sum(if(review_stars>=3,1,0)) totalhp ,\n" +
            "\t\t\t sum(if(review_stars>=3,1,0))/count(review_info.user_id) lv\n" +
            "\t\t\tfrom   review_info\n" +
            "\t   left  join   course_info   on   review_info.course_id=course_info.id\n" +
            "\t\t group  by  course_info.course_name")
    public  List<Vo2>  getList4();

}
